"""
 Model.py
"""
from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.orm import mapper, relation, clear_mappers
from sqlalchemy.exceptions import InvalidRequestError


class Character(object):
    def __init__(self, name=u'', description=u'', body=u''):
        self.name = name
        self.description = description
                
    def __repr__(self):
        return "<Character( '%s' )" % (self.name)

class Book(object):
    def __init__(self, name=u'', description=u'', body=u''):
        self.name = name
        self.description = description

    def __repr__(self):
        return "<Book('%s')" % (self.name)
        
class Chapter(object):
    def __init__(self, name=u'', description=u'', body=u''):
        self.name = name
        self.description = description

    def __repr__(self):
        return "<Chapter('%s')" % (self.name)        

class Scene(object):
    def __init__(self, name=u'', description=u'', body=u''):
        self.name = name
        self.description = description

    def __repr__(self):
        return "<Scene('%s')" % (self.name)        

class Scrap(object):
    def __init__(self, name=u'', description=u'', body=u''):
        self.name = name
        self.description = description

    def __repr__(self):
        return "<Scrap('%s')" % (self.name)

class Location(object):
    def __init__(self, name=u'', description=u'', body=u''):
        self.name = name
        self.description = description

    def __repr__(self):
        return "<Location('%s')" % (self.name)


def create_database():

    # do metadata    
    metadata = MetaData()
    character_table = Table('characters',metadata,
                    Column('id',Integer,primary_key=True),
                    Column('name',String(40)),
                    Column('description',String(255))
                )
    location_table = Table('locations',metadata,
                    Column('id',Integer,primary_key=True),
                    Column('name',String(40)),
                    Column('description',String(255))
                )                
    # books have characters + locations and contain chapters
    book_table = Table('books',metadata,
                    Column('id',Integer,primary_key=True),
                    Column('name',String(40)),
                    Column('description',String(255)),
                    Column('character_id',Integer,ForeignKey('characters.id')),
                    Column('location_id',Integer,ForeignKey('locations.id'))                    
                )
    # chapters have characters + locations and contain scenes
    chapter_table = Table('chapters',metadata,
                    Column('id',Integer,primary_key=True),
                    Column('name',String(40)),
                    Column('description',String(255)),
                    Column('character_id',Integer,ForeignKey('characters.id')),
                    Column('book_id',Integer,ForeignKey('books.id')),
                    Column('location_id',Integer,ForeignKey('locations.id'))           
                )    
    # scenes have characters + locations                
    scene_table = Table('scenes',metadata,
                    Column('id',Integer,primary_key=True),
                    Column('name',String(40)),
                    Column('description',String(255)),
                    Column('character_id',Integer,ForeignKey('characters.id')),
                    Column('chapter_id',Integer,ForeignKey('chapters.id')),
                    Column('location_id',Integer,ForeignKey('locations.id'))              
                )    
    # scraps have characters + locations                
    scrap_table = Table('scraps',metadata,
                    Column('id',Integer,primary_key=True),
                    Column('name',String(40)),
                    Column('description',String(255)),
                    Column('character_id',Integer,ForeignKey('characters.id')),
                    Column('location_id',Integer,ForeignKey('locations.id'))                    
                )                
                
    engine = create_engine('sqlite:///:memory:', echo=True)                
    metadata.create_all(engine)
    
    # do mappings
    mapper(Character, character_table,
        properties={'books':relation(Book,backref='character'),
                    'chapters':relation(Chapter,backref='character'),
                    'scenes':relation(Scene,backref='character'),
                    'scraps':relation(Scrap,backref='character')})
    mapper(Location, location_table,
        properties={'books':relation(Book,backref='location'),
                    'chapters':relation(Chapter,backref='location'),
                    'scenes':relation(Scene,backref='location'),
                    'scraps':relation(Scrap,backref='location')})                    
    mapper(Book, book_table,
            properties={'chapters':relation(Chapter,backref='book')})
    mapper(Chapter, chapter_table,
            properties={'scenes':relation(Scene,backref='chapter')})
    mapper(Scene, scene_table)
    mapper(Scrap, scrap_table)
    return engine
        
    
    
if __name__ == '__main__':
    engine = create_database()
    me = Character('Danny','blah blah blah')
    me.books.append(Book('Story of my life','The life and times of daniel greenfeld'))
    print me.books
    print me.books[0].character